﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Configuration;
//using System.Runtime.Serialization.Formatters.Binary;
using System.Data;
using System.IO;

namespace MP3Koo {
    /// <summary>
    /// 独立资源，单态类。
    /// 整个程序的数据操作平台，数据操作的唯一接口。
    /// </summary>
    public class 数据资源平台类 {
        static private 数据资源平台类 instance;
        static public 数据资源平台类 getInstance ( ) {
            if ( instance == null ) {
                lock ( typeof ( 数据资源平台类 ) ) {
                    数据资源平台类 temp = instance;
                    if ( temp == null ) {
                        lock ( typeof ( 数据资源平台类 ) ) {
                            temp = new 数据资源平台类 ( );
                        }
                        instance = temp;
                    }
                }
            }
            return instance;
        }
        readonly private DbProviderFactory dbFactory;
        readonly private DataSet dataSet = new DataSet ( );
        readonly private DbDataAdapter 音乐文件夹数据适配器;
        readonly private DbDataAdapter 系统信息数据适配器;

        public TaskSaver GetTaskSaver(){
            return TaskSaver.getInstance ( );
        }
        public 数据资源平台类 ( ) {
            dbFactory = DbProviderFactories.GetFactory ( ConfigurationManager.AppSettings["provider"] );

            DbConnection conn = getConnection ( );
            //音乐文件夹操作
            音乐文件夹数据适配器 = dbFactory.CreateDataAdapter ( );
            DataTable dt = new DataTable ( "t_mp3_dir" );
            DataColumn key = new DataColumn ( "Name", System.Type.GetType ( "System.String" ) );
            dt.Columns.Add ( key );

            dt.PrimaryKey = new DataColumn[] { key };//查找时必须的
            dataSet.Tables.Add ( dt );


            音乐文件夹数据适配器.SelectCommand = conn.CreateCommand ( );
            音乐文件夹数据适配器.SelectCommand.CommandText = "select Name,Label,Path,Type from t_mp3_dir";

            音乐文件夹数据适配器.UpdateCommand = conn.CreateCommand ( );
            音乐文件夹数据适配器.UpdateCommand.CommandText = "update t_mp3_dir set Label=? where Name=?";
            DbParameter param = dbFactory.CreateParameter ( );
            param.ParameterName = "@Label";
            param.SourceColumn = "Label";
            音乐文件夹数据适配器.UpdateCommand.Parameters.Add ( param );
            param = dbFactory.CreateParameter ( );
            param.ParameterName = "@Name";
            param.SourceColumn = "Name";
            param.SourceVersion = DataRowVersion.Original;
            音乐文件夹数据适配器.UpdateCommand.Parameters.Add ( param );

            音乐文件夹数据适配器.InsertCommand = conn.CreateCommand ( );
            音乐文件夹数据适配器.InsertCommand.CommandText = "insert into t_mp3_dir (Name,Label,Path,Type) values (?,?,?,?)";
            param = dbFactory.CreateParameter ( );
            param.ParameterName = "@Name";
            param.SourceColumn = "Name";
            音乐文件夹数据适配器.InsertCommand.Parameters.Add ( param );
            param = dbFactory.CreateParameter ( );
            param.ParameterName = "@Label";
            param.SourceColumn = "Label";
            音乐文件夹数据适配器.InsertCommand.Parameters.Add ( param );
            param = dbFactory.CreateParameter ( );
            param.ParameterName = "@Path";
            param.SourceColumn = "Path";
            音乐文件夹数据适配器.InsertCommand.Parameters.Add ( param );
            param = dbFactory.CreateParameter ( );
            param.ParameterName = "@Type";
            param.SourceColumn = "Type";
            音乐文件夹数据适配器.InsertCommand.Parameters.Add ( param );

            音乐文件夹数据适配器.DeleteCommand = conn.CreateCommand ( );
            音乐文件夹数据适配器.DeleteCommand.CommandText = "delete from t_mp3_dir where Name=?";
            param = dbFactory.CreateParameter ( );
            param.ParameterName = "@Name";
            param.SourceColumn = "Name";
            param.DbType = DbType.String;
            param.SourceVersion = DataRowVersion.Original;
            音乐文件夹数据适配器.DeleteCommand.Parameters.Add ( param );

            //系统信息操作
            dt = new DataTable ( "t_system_info" );
            key = new DataColumn ( "Name", System.Type.GetType ( "System.String" ) );
            dt.Columns.Add ( key );
            dt.PrimaryKey = new DataColumn[] { key };//查找时必须的
            dataSet.Tables.Add ( dt );

            系统信息数据适配器 = dbFactory.CreateDataAdapter ( );
            系统信息数据适配器.SelectCommand = conn.CreateCommand ( );
            系统信息数据适配器.SelectCommand.CommandText = "select [Value] from t_mp3_dir";
        }

        private DbConnection getConnection ( ) {
            DbConnection conn = dbFactory.CreateConnection ( );
            conn.ConnectionString = ConfigurationManager.AppSettings["connectionString"];
            conn.Open ( );
            return conn;
        }

        /// <summary>
        /// 从数据库中读取给定名称的设定。
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public string readSystemInfo ( string name ) {
            //using (DbConnection connection = getDbConnection())
            //{
            //    try
            //    {
            //        DbCommand command = connection.CreateCommand();
            //        command.CommandText = "select value from t_system_info where name='" + name + "'";
            //        DbDataReader reader = command.ExecuteReader();
            //        while (reader.Read())
            //        {
            //            string value = reader.GetString(0);
            //            return value;
            //        }
            //        throw new Exception("db t_system_info not writeRecord mp3dir info!");
            //    }
            //    catch (Exception e)
            //    {
            //        throw new Exception(e.Message);
            //    }
            //}
            return null;
        }

        ///// <summary>
        ///// 把给定的系统设定写入数据库。如无指定设置名称，将发生错误。
        ///// </summary>
        ///// <param name="name"></param>
        ///// <param name="value"></param>
        //private void writeSystemInfo(string name, string value)
        //{
        //    using (DbConnection connection = getDbConnection())
        //    {
        //        try
        //        {
        //            DbCommand command = connection.CreateCommand();
        //            command.CommandText = "select value from t_system_info where name='" + name + "'";
        //            DbDataReader reader = command.ExecuteReader();
        //            while (reader.Read())
        //            {
        //                //更新
        //                string oldValue = reader.GetString(0);//oldName;
        //                if (oldValue.Equals(value))
        //                {
        //                    return;
        //                }
        //                command.CommandText = "update t_system_info set value='" + value + "' where name='" + name + "'";
        //                command.ExecuteNonQuery();
        //                return;
        //            }
        //            //插入
        //            command.CommandText = "insert into t_system_info(name,value) values ('" + name + "','" + value + ")";
        //            command.ExecuteNonQuery();
        //        }
        //        catch (Exception e)
        //        {
        //            throw new Exception(e.Message);
        //        }
        //    }
        //}
        public ICollection<音乐文件夹类> 获取音乐文件夹 ( ) {
            IList<音乐文件夹类> list = new List<音乐文件夹类> ( );
            音乐文件夹数据适配器.Fill ( dataSet, "t_mp3_dir" );
            foreach ( DataRow r in dataSet.Tables["t_mp3_dir"].Rows ) {
                音乐文件夹类 文件夹 = new 音乐文件夹类 ( );
                文件夹.名称 = r["Name"].ToString ( );
                文件夹.标签 = r["Label"].ToString ( );
                文件夹.路径 = r["Path"].ToString ( );
                文件夹.类型 = r["Type"].ToString ( ).Equals ( "NET", StringComparison.CurrentCultureIgnoreCase ) ? 音乐文件夹类.文件类型.网络 : 音乐文件夹类.文件类型.本地;

                检查修正文件夹数据 ( 文件夹 );
                list.Add ( 文件夹 );
            }
            return list;
        }
        public bool 添加音乐文件夹 ( 音乐文件夹类 文件夹 ) {
            try {
                DataTable dt = dataSet.Tables["t_mp3_dir"];
                DataRow row = dt.NewRow ( );
                row["Name"] = 文件夹.名称;
                row["Label"] = 文件夹.标签;
                row["Path"] = 文件夹.路径;
                row["Type"] = ( 文件夹.类型 == 音乐文件夹类.文件类型.网络 ) ? "NET" : "LOCAL";
                dt.Rows.Add ( row );
                音乐文件夹数据适配器.Update ( dataSet, "t_mp3_dir" );
                return true;
            } catch ( Exception e ) {
                Console.WriteLine ( e );
            }
            return false;
        }
        public bool 删除音乐文件夹 ( string name ) {
            try {
                DataTable dt = dataSet.Tables["t_mp3_dir"];
                DataRow dr = dt.Rows.Find ( name );
                if ( dr == null ) {
                    return false;
                }
                dr.Delete ( );//注意这里，不应该使用 dt.rows.romove(dr);                
                音乐文件夹数据适配器.Update ( dataSet, "t_mp3_dir" );
                return true;
            } catch ( Exception e ) {
                Console.WriteLine ( e );
            }
            return false;
        }
        /// <summary>
        /// 对于本地文件，不存在实际目录的要创建
        /// 并把路径修改为绝对路径
        /// </summary>
        /// <param name="文件夹"></param>
        /// <returns></returns>
        private bool 检查修正文件夹数据 ( 音乐文件夹类 文件夹 ) {
            try {
                if ( 文件夹.类型 == 音乐文件夹类.文件类型.本地 ) {
                    if ( !Directory.Exists ( 文件夹.路径 ) ) {
                        Directory.CreateDirectory ( 文件夹.路径 );
                    }
                    DirectoryInfo di = new DirectoryInfo ( 文件夹.路径 );
                    文件夹.路径 = di.FullName;
                }
                return true;
            } catch ( Exception e ) {
                Console.WriteLine ( e );
            }
            return false;
        }

        //
        public bool 检索音乐类型扩展名 ( string 扩展名 ) {
            Dictionary<string, string> d = new Dictionary<string, string> ( );
            d.Add ( ".mp3", null );
            d.Add ( ".wma", null );
            d.Add ( ".rm", null );
            return d.ContainsKey ( 扩展名.ToLower ( ) );
        }



        public List<KeyValuePair<int, String>> 获取网络音乐文件集合 ( int from, int len ) {
            return 获取网络音乐文件 ( from, len, null );
        }
        public List<KeyValuePair<int, string>> 获取网络音乐文件 ( int from, int len, string type ) {
            using ( DbConnection connection = getConnection ( ) ) {
                try {
                    int top = from + len;
                    DbCommand command = connection.CreateCommand ( );
                    command.CommandText = "select " + ( ( top <= 0 ) ? "" : ( " top " + top + " " ) ) + " id,filename from t_mp3_net_file " + ( type == null ? " " : ( " where t_mp3_net_file.[type] ='" + type + "' order by t_mp3_net_file.[Order];" ) );
                    //Console.WriteLine(command.CommandText);
                    DbDataReader reader = command.ExecuteReader ( );
                    int i = 0;
                    List<KeyValuePair<int, String>> retu = new List<KeyValuePair<int, string>> ( );
                    while ( reader.Read ( ) ) {
                        i++;
                        if ( i < from ) {
                            continue;
                        }
                        int id = reader.GetInt32 ( 0 );
                        string fileName = reader.GetString ( 1 );
                        retu.Add ( new KeyValuePair<int, string> ( id, fileName ) );
                    }
                    return retu;
                } catch ( Exception e ) {
                    throw new Exception ( e.Message );
                }
            }
        }
        public ICollection<string> 获取网络音乐文件类型 ( ) {
            using ( DbConnection connection = getConnection ( ) ) {
                try {
                    DbCommand command = connection.CreateCommand ( );
                    command.CommandText = "select distinct [type] from t_mp3_net_file";
                    DbDataReader reader = command.ExecuteReader ( );

                    List<String> retu = new List<string> ( );
                    while ( reader.Read ( ) ) {
                        string type = reader.GetString ( 0 );
                        retu.Add ( type );
                    }
                    return retu;
                } catch ( Exception e ) {
                    throw new Exception ( e.Message );
                }
            }
        }

        public class 网络音乐文件 {
            private int id;
            private string name;
            private string uri;
            private string file;
            private string type;
            private int order;

            public int ID {
                get {
                    return id;
                }
                set {
                    id = value;
                }
            }
            public string Name {
                get { return name; }
                set { name = value; }
            }
            public string URI {
                get { return uri; }
                set { uri = value; }
            }
            public string Type {
                get { return type; }
                set { type = value; }
            }
            public string File {
                get { return file; }
                set { file = value; }
            }
            public int Order {
                get { return order; }
                set { order = value; }
            }
        }
        public 网络音乐文件 获取网络音乐文件 ( int id ) {
            using ( DbConnection connection = getConnection ( ) ) {
                try {
                    DbCommand command = connection.CreateCommand ( );
                    command.CommandText = "select [id],[filename],[uri],[file],[type],[order] from t_mp3_net_file where t_mp3_net_file.[id]=" + id;
                    DbDataReader reader = command.ExecuteReader ( );
                    if ( reader.Read ( ) ) {
                        网络音乐文件 mp3 = new 网络音乐文件 ( );
                        mp3.ID = reader.GetInt32 ( 0 );
                        mp3.Name = reader.GetString ( 1 );
                        mp3.URI = reader.IsDBNull(2)?null:reader.GetString ( 2 );
                        mp3.File = reader.IsDBNull ( 3 ) ? null : reader.GetString ( 3 );
                        mp3.Type = reader.GetString ( 4 );
                        mp3.Order = reader.GetInt32 ( 5 );
                        return mp3;
                    }
                    return null;
                } catch ( Exception e ) {
                    throw new Exception ( e.Message );
                }
            }
        }
    }//~saver   
}
